<?php

namespace App\Http\Classes\Excel;

use PHPExcel;
use PHPExcel_Writer_Excel2007;
use PHPExcel_Cell_DataType;
use PHPExcel_Style_Fill;
use PHPExcel_Style_Alignment;

class InputExcel
{
    public $objPHPExcel;
    public $objWrite;

    public function __construct()
    {
        $this->objPHPExcel = new PHPExcel();
        $this->objWrite = new PHPExcel_Writer_Excel2007($this->objPHPExcel);
    }

    /**
     * @param $data                     写入数据
     * @param string $titleheader       表头数据
     * @param int $titleline            表头所在行
     * @param bool $dispage             是否分页
     * @param int $pagesize             分页步长
     */
    public function inputexcel($filename,$data,$titleheader='title',$titleline=2,$dispage=true,$pagesize=10000)
    {
        set_time_limit(0);
        ini_set('memory_limit', '128M');

        $letter = array('A', 'B', 'C', 'D', 'E', 'F', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S');
        $tableheader = ['省', '市', '乡', '镇', '村'];

        $con_count = count($data);
        if( $con_count>$pagesize )
        {
            $pageid = intval( $con_count / $pagesize );
        }else{
            $pageid = 1;
        }

//        dd($pageid);
        $lastword = $this->titlerange($tableheader);
        //添加写入excel开始时间戳
        $titleheader = time();
//        dd($data);

        if( $pageid == 1 || $pagesize<4000 && !$dispage ){
            $this->headerstyle($letter, $lastword, $tableheader, $titleheader, $titleline);
            $this->bodystyle($lastword, $con_count, $titleline);
            for ($i = ($titleline+1);$i <= $con_count + $titleline;$i++)
            {
                $j = 0;
                foreach ($data[$i - ($titleline+1)] as $key=>$value)
                {
                    $this->objPHPExcel->getActiveSheet()->setCellValueExplicit("$letter[$j]$i","$value",\PHPExcel_Cell_DataType::TYPE_STRING);
                    $j++;
                }
            }
        }
        else if( $pageid > 1 || $pagesize >= 4000 && $dispage ){
            for ($sheet = 0; $sheet < $pageid; $sheet++)
            {
                $this->objPHPExcel->createSheet();
                $this->objPHPExcel->setactivesheetindex($sheet);

                if( $pageid == 1 )
                {
                    $start = $sheet*$pagesize;
                    $end = $con_count;
                }else{
                    $start = $sheet*$pagesize;

                    if( $sheet == $pageid-1 ){
                        $end = $con_count;
                    }else{
                        $end = ($sheet+1) * $pagesize;
                    }
                }

                $this->headerstyle($letter, $lastword, $tableheader, $titleheader, $titleline);
                $this->bodystyle($lastword, $con_count, $titleline);
//                echo $start,'<br>';

                $i = ($titleline + 1);
                for($u = $start;$u<$end;$u++){
                    $j = 0;
                    foreach ($data[$u] as $k=>$value)
                    {
                        $this->objPHPExcel->getActiveSheet()->setCellValueExplicit("$letter[$j]$i","$value",PHPExcel_Cell_DataType::TYPE_STRING);
                        $j++;
                    }
                    $i++;
                }
            }
        }


        //添加写入excel结束时间戳
        $cellconf = "A".($con_count+$titleline);
        $this->objPHPExcel->getActiveSheet()->setCellValueExplicit("$cellconf",time(),PHPExcel_Cell_DataType::TYPE_STRING);

//        $filename = date('Ymd').uniqid();

        ob_end_clean();//清除缓冲区,避免乱码
        header('Content-Type: application/vnd.ms-excel');
//        header('Content-Disposition: attachment;filename='.$filename.'.xlsx');
        header('Cache-Control: max-age=0');
        $this->objWrite->save('./module/jifenbao/onload/'.$filename.'.xlsx');return true;
//        $this->objWrite->save('php://output');
        /*$excelpath = './'.$filename.'.xlsx';
        $this->objWrite->save( $excelpath );*/
        exit;

    }

    /**
     * 设定表头部分格式 并 写入表顶+表头数据
     * @param $letter     字母列
     * @param $lastword   最后列字母
     * @param $tableheader  表顶部
     * @param $titleheader  表头
     * @param $titleline    表头所在行
     */
    private function headerstyle($letter,$lastword,$tableheader,$titleheader,$titleline)
    {
        //设置字体
        $this->objPHPExcel->getDefaultStyle()->getFont()->setName( '微软雅黑');

        //合并单元格
        $this->objPHPExcel->getActiveSheet()->mergeCells( 'A1:' . $lastword . '1');
        $this->objPHPExcel->getActiveSheet()->setCellValue("A1",$titleheader,PHPExcel_Cell_DataType::TYPE_STRING);

        //转化为数字格式
//        $this->objPHPExcel->getActiveSheet()->getStyle('F'.$iLineNumber)->getNumberFormat()->setFormatCode('0.000');

        for($i = 0;$i < count($tableheader);$i++)
        {
            $this->objPHPExcel->getActiveSheet()->setCellValue("$letter[$i]".$titleline ,"$tableheader[$i]",PHPExcel_Cell_DataType::TYPE_STRING);
        }

        //设置第一行的行高
        $this->objPHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(30);
        //表头加粗&水平垂直居中
        $this->objPHPExcel->getActiveSheet()->getStyle('A'.$titleline.':' . $lastword . $titleline)->applyFromArray(array(
                'font' => array (
                    'bold' => true
                )/*,
	                'alignment' => array(
	                    'horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                        'vertical' => \PHPExcel_Style_Alignment::VERTICAL_CENTER
	                )*/
            )
        );

        //表头所在行的背景颜色
        $this->objPHPExcel->getActiveSheet()->getStyle('A'. $titleline .':' . $lastword . $titleline)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
        $this->objPHPExcel->getActiveSheet()->getStyle('A'. $titleline .':' . $lastword . $titleline)->getFill()->getStartColor()->setARGB('ff0000');
    }

    /**
     * 表内容样式
     * @param $lastword   最大列所在字母
     * @param $con_count  最大记录数
     * @param $titleline  表头所在行
     */
    private function bodystyle($lastword,$con_count,$titleline)
    {
        //设置单元格的宽度
        $this->objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
        $this->objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
        $this->objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
        $this->objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
        $this->objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20);

        //表中全部数据水平垂直居中
        $this->objPHPExcel->getActiveSheet()->getStyle( 'A1:'.$lastword.($con_count+$titleline) )->applyFromArray(array(
                'alignment' => array(
                    'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                    'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER
                )
            )
        );

    }

    //计算最大宽度所在列字母
    private function titlerange($title)
    {
        $num = count($title);
        for( $i=0;$i<$num;$i++ )
        {
            $arr[$i] = chr($i+65);
        }
        return array_pop($arr);
    }

    //转utf8编码
    public function turnutf8($str)
    {
        $encode = mb_detect_encoding($str, array("ASCII","UTF-8","GB2312","GBK","BIG5"));
        if($encode != "UTF-8")
            return iconv($encode,"UTF-8",$str);
        return $str;
    }
}


